package com.starsky.common.utils.easypoi;

import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.csv.entity.CsvImportParams;
import cn.afterturn.easypoi.csv.export.CsvExportService;
import cn.afterturn.easypoi.csv.imports.CsvImportService;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.afterturn.easypoi.excel.imports.sax.SaxReadExcel;
import cn.afterturn.easypoi.exception.excel.ExcelImportException;
import cn.afterturn.easypoi.handler.inter.IReadHandler;
import com.starsky.common.utils.DateUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;

/**
 * Excel工具类
 */
public class ExcelUtils {

    /**
     * Excel导入单个sheet数据
     * 导入Excel转换为List<T>
     *
     * @param filePath   文件名
     * @param titleRows  标头行，表示从第几行开始，默认从0开始
     * @param headerRows 标头行，表示从第几列开始，默认从1开始
     * @param clazz      对象Class
     */
    public static <T> List<T> importExcel(String filePath, int titleRows, int headerRows, Class<T> clazz) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), clazz, params);
        } catch (Exception e) {
            e.printStackTrace();

        }
        return list;
    }

    /**
     * Excel导入单个sheet数据
     * 导入Excel转换为List<T>
     *
     * @param file       http上传的文件
     * @param titleRows  标头行，表示从第几行开始，默认从0开始
     * @param headerRows 标头行，表示从第几列开始，默认从1开始
     * @param clazz      对象Class
     */
    public static <T> List<T> importExcel(MultipartFile file, int titleRows, int headerRows, Class<T> clazz) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), clazz, params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * Excel 导入 数据源本地文件,不返回校验结果 导入 字 段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param file
     * @param pojoClass
     * @param params
     * @return
     */
    public static <T> List<T> importExcel(File file, Class<?> pojoClass, ImportParams params) {
        FileInputStream in = null;
        try {
            in = new FileInputStream(file);
            List<T> list = ExcelImportUtil.importExcel(file, pojoClass, params);
            return list;
        } catch (Exception e) {
            throw new ExcelImportException(e.getMessage(), e);
        } finally {
            IOUtils.closeQuietly(in);
        }
    }

    /**
     * Excel 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param inputstream
     * @param pojoClass
     * @param params
     * @return
     * @throws Exception
     */
    public static <T> List<T> importExcel(InputStream inputstream, Class<?> pojoClass,
                                          ImportParams params) throws Exception {
//        return new ExcelImportService().importExcelByIs(inputstream, pojoClass, params, false).getList();
        return ExcelImportUtil.importExcel(inputstream, pojoClass, params);
    }


    /**
     * 依然建议上面的写法,但不建议使用校验,可以加入下面参数加快时间,但是因为是一次返回可能量还是有点大,不支持一对多
     * <p>
     * Excel 通过SAX解析方法,适合大数据导入,不支持图片
     * 导入 数据源本地文件,不返回校验结果 导入 字 段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param inputstream
     * @param pojoClass
     * @param params:     量稍微有点大10W内, 增加concurrentTask :是否并行计算
     * @param handler
     */
    public static <T> List<T> importExcelBySax(InputStream inputstream, Class<?> pojoClass,
                                               ImportParams params, IReadHandler handler) {
        List<T> list = null;
        try {
            list = new SaxReadExcel().readExcel(inputstream, pojoClass, params, handler);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 更加大量的数据,百万级以上
     * 使用csv把,这个靠谱的多,百万级读取数据也相当的快,内存也不高
     * Csv 导入流适合大数据导入
     * 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param inputstream
     * @param pojoClass
     * @param params
     * @return
     */
    public static <T> List<T> importCsv(InputStream inputstream, Class<?> pojoClass,
                                        CsvImportParams params) {
        return new CsvImportService().readExcel(inputstream, pojoClass, params, null);
    }

    /**
     * Csv 导入流适合大数据导入
     * 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
     *
     * @param inputstream
     * @param pojoClass
     * @param params
     * @return
     */
    public static <T> List<T> importCsv(InputStream inputstream, Class<?> pojoClass,
                                        CsvImportParams params, IReadHandler readHandler) {
        List<T> list = new CsvImportService().readExcel(inputstream, pojoClass, params, readHandler);
        return list;
    }

    /**
     * @param params    表格标题属性
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static void exportCsv(CsvExportParams params, Class<?> pojoClass,
                                 Collection<?> dataSet, OutputStream outputStream) {
        new CsvExportService().createCsv(outputStream, params, pojoClass, dataSet);
    }

    /**
     * 根据Map创建对应的Excel
     *
     * @param params     表格标题属性
     * @param entityList Map对象列表
     * @param dataSet    Excel对象数据List
     */
    public static void exportCsv(CsvExportParams params, List<ExcelExportEntity> entityList,
                                 Collection<?> dataSet, OutputStream outputStream) {
        new CsvExportService().createCsvOfList(outputStream, params, entityList, dataSet);
    }


    /**
     * Excel导出单个sheet
     *
     * @param entity    表格标题属性
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {
        Workbook workbook = ExcelExportUtil.exportExcel(entity, pojoClass, dataSet);
        return workbook;
    }


    /**
     * Excel导出单个sheet
     *
     * @param entity    表格标题属性
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static void exportExcel(Workbook workbook, ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {
        (new ExcelExportService()).createSheet(workbook, entity, pojoClass, dataSet);
    }

    /**
     * Excel导出单个sheet
     *
     * @param response response
     * @param fileName 文件名
     * @param workbook 创建导出excel
     */
    public static void writeResponse(HttpServletResponse response, Workbook workbook, String fileName) {

        try {
            Sheet sheet1 = workbook.getSheetAt(0);
            sheet1.setDefaultColumnWidth(50 * 256);
            sheet1.setDefaultRowHeight((short) (2 * 256));
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * Excel导出单个sheet
     *
     * @param response  response
     * @param fileName  文件名
     * @param list      数据List
     * @param pojoClass 对象Class
     */
    public static void exportExcel(HttpServletResponse response, ExportParams exportParams,
                                   String fileName, Collection<?> list,
                                   Class<?> pojoClass) {
        if (StringUtils.isBlank(fileName)) {
            // 当前日期
            fileName = DateUtils.format(new Date(), null);
        }

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        ExcelUtils.writeResponse(response, workbook, fileName);
    }

    /**
     * Excel导出单个sheet
     *
     * @param response  response
     * @param fileName  文件名
     * @param list      数据List
     * @param pojoClass 对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
                                   Class<?> pojoClass) {
        if (StringUtils.isBlank(fileName)) {
            // 当前日期
            fileName = DateUtils.format(new Date(), null);
        }

        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), pojoClass, list);
        ExcelUtils.writeResponse(response, workbook, fileName);
    }


    /**
     * Excel导出sheet，先sourceList转换成List<targetClass>，再导出
     *
     * @param response  response
     * @param fileName  文件名
     * @param dataList  原数据List
     * @param pojoClass 目标对象Class
     */
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, Collection<?> dataList,
                                           Class<?> pojoClass) throws Exception {
        List<Object> targetList = new ArrayList<>(dataList.size());
        for (Object source : dataList) {
            Object target = pojoClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }
        ExcelUtils.exportExcel(response, fileName, targetList, pojoClass);
    }


    /**
     * 创建sheet需要的信息
     *
     * @param exportParams：sheet相关参数
     * @param entity：导出sheet对应class类
     * @param data：导出数据
     * @return
     */
    public static Map<String, Object> getSheetMap(ExportParams exportParams, Object entity, List data) {
        Map<String, Object> map = new HashMap<>();
        map.put("title", exportParams);
        map.put("entity", entity);
        map.put("data", data);
        return map;
    }

    /**
     * Execl导出多个sheet
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     *
     * @param dataList list: 多个Map ,
     *                 Map<String, Object> map = new HashMap<>();
     *                 map.put("title", ExportParams);
     *                 map.put("entity", 到处实体类class);
     *                 map.put("data", list集合数据);
     *                 list.add(map);
     *                 每个map中必须保护key为title，即对应表格Title；
     *                 key为 entity，即对应表格对应实体
     *                 key为data，即Collection数据
     * @param filename 文件名
     */
    public static void exportExcel(List<Map<String, Object>> dataList, String filename) {
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(dataList, ExcelType.HSSF);
            FileOutputStream fos = new FileOutputStream(filename);
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Execl导出多个sheet
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     *
     * @param dataList     list: 多个Map ,
     *                     Map<String, Object> map = new HashMap<>();
     *                     map.put("title", ExportParams);
     *                     map.put("entity", 到处实体类class);
     *                     map.put("data", list集合数据);
     *                     list.add(map);
     *                     每个map中必须保护key为title，即对应表格Title；
     *                     key为 entity，即对应表格对应实体
     *                     key为data，即Collection数据
     * @param outputStream 输出文件流
     */
    public static void exportExcel(List<Map<String, Object>> dataList, OutputStream outputStream) {
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(dataList, ExcelType.HSSF);
            workbook.write(outputStream);
            outputStream.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * Execl导出多个sheet
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     *
     * @param dataList list: 多个Map ,
     *                 Map<String, Object> map = new HashMap<>();
     *                 map.put("title", ExportParams);
     *                 map.put("entity", 到处实体类class);
     *                 map.put("data", list集合数据);
     *                 list.add(map);
     *                 每个map中必须保护key为title，即对应表格Title；
     *                 key为 entity，即对应表格对应实体
     *                 key为data，即Collection数据
     * @param filename 文件名
     */
    public static void exportExcel(HttpServletResponse response, List<Map<String, Object>> dataList, String filename) {
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(dataList, ExcelType.HSSF);
            ExcelUtils.writeResponse(response, workbook, filename);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出excel
     *
     * @param pojoClass
     * @param dataSet
     * @param path
     * @param filename
     */
    public static void exportExcel(Class<?> pojoClass, Collection<?> dataSet, String path, String filename) {

        try {
            File savefile = new File(path);
            if (!savefile.exists()) {
                savefile.mkdirs();
            }
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), pojoClass, dataSet);
            FileOutputStream fos = new FileOutputStream(path + File.separator + filename);
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出excel
     *
     * @param pojoClass
     * @param dataSet
     * @param path
     * @param filename
     */
    public static void exportExcel(Class<?> pojoClass, Collection<?> dataSet, ExportParams exportParams, String path, String filename) {

        try {
            File savefile = new File(path);
            if (!savefile.exists()) {
                savefile.mkdirs();
            }
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, dataSet);
            FileOutputStream fos = new FileOutputStream(path + File.separator + filename);
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 根据Map创建对应的Excel(一个excel 创建多个sheet)
     *
     * @param list     list 多个Map key title 对应表格Title key entity 对应表格对应实体 key data
     *                 *             Collection 数据
     * @param path     路径
     * @param filename 　文件名
     */
    public static void exportExcel(List<Map<String, Object>> list, String path, String filename) {
        try {
            File savefile = new File(path);
            if (!savefile.exists()) {
                savefile.mkdirs();
            }
            Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
            FileOutputStream fos = new FileOutputStream(path + File.separator + filename);
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建sheet需要的信息
     *
     * @param title：sheet       标题
     * @param sheetName：sheet名称
     * @return
     */
    public static ExportParams getExportParams(String title, String sheetName) {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(title);
        exportParams.setSheetName(sheetName);
        return exportParams;
    }

    /**
     * 动态列头级数据导出
     *
     * @param response:     写入浏览器
     * @param filename：文件名称
     * @param colList：列名
     * @param dataList：数据
     */
    public static void exportDynaCol(HttpServletResponse response, String filename,
                                     ExportParams exportParams,
                                     List<ExcelExportEntity> colList,
                                     List<Map<String, Object>> dataList) {
        try {
//            new ExportParams("价格分析表", "数据")
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, colList, dataList);
            ExcelUtils.writeResponse(response, workbook, filename);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 动态列头级数据导出
     *
     * @param exportParams：excel参数设置
     * @param colList：列名
     * @param dataList：数据
     */
    public static Workbook exportDynaCol(ExportParams exportParams,
                                         List<ExcelExportEntity> colList,
                                         List<Map<String, Object>> dataList) {
//            new ExportParams("价格分析表", "数据")
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, colList, dataList);
        return workbook;
    }

    /**
     * 动态列头级数据导出
     *
     * @param exportParams：excel参数设置
     * @param colList：列名
     * @param dataList：数据
     */
    public static void exportDynaCol(Workbook workbook, ExportParams exportParams,
                                     List<ExcelExportEntity> colList,
                                     List<Map<String, Object>> dataList) {
        new ExcelExportService().createSheetForMap(workbook, exportParams, colList, dataList);
    }

    /**
     * 创建workbook
     *
     * @param type:sheet类型（HSSF、XSSF）
     * @param size：sheet存储数据最大值，不同版本sheet容量各不相同
     * @return
     */
    public static Workbook getWorkbook(ExcelType type, int size) {
        if (ExcelType.HSSF.equals(type)) {
            return new HSSFWorkbook();
        } else {
            return (Workbook) (size < ExcelExportUtil.USE_SXSSF_LIMIT ? new XSSFWorkbook() : new SXSSFWorkbook());
        }
    }


    public static void main(String[] args) {

        //读取文件
//        List<ExcelTestEntity> dtos = ExcelUtils.importExcel(file, 0, 1, ExcelTestEntity.class);
//        List<ExcelTestEntity> dtos = ExcelImportUtil.importExcel(inputStream, ExcelTestEntity.class, params);

        //导出
//        List<ExcelTestEntity> list = new ArrayList<>();
//        ExcelTestEntity excelTest = new ExcelTestEntity();
//        excelTest.setId(10000);
//        excelTest.setFileName("测试excel.xlsx");
//        excelTest.setFilePath("c:\\excel");
//        excelTest.setType(1);
//        list.add(excelTest);
//        ExcelUtils.exportExcelToTarget(response, null, list, ExcelTestEntity.class);
    }


    /**
     * 测试多sheet导出
     */
    public void testExportExcels() throws Exception {
        List<Map<String, Object>> list = new ArrayList<>();
        for (int n = 1; n < 4; n++) {
            ExportParams exportParams = new ExportParams("导出多个sheet" + n, "sheet" + n);
            Object entity = ExcelTestEntity.class;
            List<ExcelTestEntity> data = new ArrayList<>();
            int i = 0;
            while (i < 10) {
                ExcelTestEntity user = new ExcelTestEntity();
                user.setId(i * n + 1);
                user.setFileName("导出多个sheet" + i);
                user.setFilePath("导出多个sheet" + i);
                user.setType(i);
                user.setCreatorName("导出多个sheet" + i);
                user.setCreateDate(new Date());
                data.add(user);
                i++;
            }
            // 构建多个sheet 格式map数据
            Map<String, Object> map = new HashMap<>();
            map.put("title", exportParams);
            map.put("entity", entity);
            map.put("data", data);
            list.add(map);
        }
        ExcelUtils.exportExcel(list, "user1.xls");
    }


    /**
     * 动态列头级数据导出
     */
    @Test
    public void testDynaCol() {
        try {
            List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
            ExcelExportEntity colEntity = new ExcelExportEntity("商品名称", "title");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
            colEntity = new ExcelExportEntity("供应商", "supplier");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            //1、动态列
            for (int i = 0; i <= 2; i++) {
                ExcelExportEntity deliColGroup = new ExcelExportEntity("淘宝-" + i, "detail" + i);
                List<ExcelExportEntity> deliColList = new ArrayList<ExcelExportEntity>();
                deliColList.add(new ExcelExportEntity("市场价", "orgPrice" + i));
                deliColList.add(new ExcelExportEntity("专区价", "salePrice" + i));
                deliColGroup.setList(deliColList);
                colList.add(deliColGroup);
            }

            //2、动态列数据1
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            for (int i = 0; i <= 2; i++) {
                Map<String, Object> valMap = new HashMap<String, Object>();
                valMap.put("title", "名称." + i);
                valMap.put("supplier", "供应商." + i);

                //表头固定，供用商有多个
                for (int k = 0; k <= 2; k++) {
                    List<Map<String, Object>> deliDetailList = new ArrayList<Map<String, Object>>();
                    Map<String, Object> deliValMap = new HashMap<String, Object>();
                    deliValMap.put("orgPrice" + k, "orgPrice." + k);
                    deliValMap.put("salePrice" + k, "salePrice." + k);
                    deliDetailList.add(deliValMap);
                    valMap.put("detail" + k, deliDetailList);
                }
                list.add(valMap);
            }

            //第一个sheet
            ExportParams entity = new ExportParams("价格分析表", "数据");
            entity.setType(ExcelType.XSSF);
            Workbook workbook = ExcelUtils.getWorkbook(entity.getType(), ExcelExportUtil.USE_SXSSF_LIMIT);
            ExcelExportService service = new ExcelExportService();
            service.createSheetForMap(workbook, entity, colList, list);
//            Workbook workbook1 = ExcelUtils.exportDynaCol(entity, colList, list);

            //第二个sheet
            ExportParams entit2 = new ExportParams("厂商排名", "数据");
            List<ExcelTestEntity> dataList2 = new ArrayList<>();
            for (int i = 0; i <= 9; i++) {
                ExcelTestEntity targetDto = new ExcelTestEntity();
                targetDto.setId(i);
                targetDto.setFileName("文件名-" + i);
                targetDto.setFilePath("文件路径-" + i);
                targetDto.setType(i);
                targetDto.setCreatorName("王五-" + i);
                targetDto.setCreateDate(new Date());
                dataList2.add(targetDto);
            }
            service.createSheet(workbook, entit2, ExcelTestEntity.class, dataList2);
//            ExcelUtils.exportExcel(workbook, entit2, ExcelTestEntity.class, dataList2);
//            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("价格分析表", "数据"), colList, list);
            //将生成excel输出到本地存储
            FileOutputStream fos = new FileOutputStream("D:/价格分析表.tt.xls");
            workbook.write(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


}
